package cn.enilu.flash.dao.shop;


import cn.enilu.flash.bean.entity.shop.Goods;
import cn.enilu.flash.dao.BaseRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;


public interface GoodsRepository extends BaseRepository<Goods,Long>{

    @Query(nativeQuery = true, value = "select * from \n" +
            "(\n" +
            "\tselect \n" +
            "\tA.id as goodId,\n" +
            "\tA.name,\n" +
            "\tA.id_category as categoryId,\n" +
            "\t(select name from  t_shop_category where id = A.id_category) as categoryName,\n" +
            "\tA.is_on_sale  as isOnSale,\n" +
            "\tA.stock,\n" +
            "\tA.price,\n" +
            "\tA.discount_desc as discountDesc ,\n" +
            "\tA.discount ,\n" +
            "\tA.discount_price as discountPrice,\n" +
            "\tA.discount_price_start as discountPriceStart,\n" +
            "\tA.discount_price_end as discountPriceEnd,\n" +
            "\t'' as code,\n" +
            "\t'' as codeName,\n" +
            "\tnull as skuId\n" +
            "\tfrom t_shop_goods A\n" +
            "\twhere A.disable  = 0\n" +
            "\tand A.is_delete  = 0\n" +
            "\tand A.id_category = ?1 or ?1 is null\n" +
            "\tand A.name like concat('%',?2,'%') \n" +
            "\tunion\n" +
            "\tselect \n" +
            "\tA.id as goodId,\n" +
            "\tA.name,\n" +
            "\tA.id_category as categoryId,\n" +
            "\t(select name from  t_shop_category where id = A.id_category) as categoryName,\n" +
            "\tA.is_on_sale  as isOnSale,\n" +
            "\tB.stock,\n" +
            "\tB.price,\n" +
            "\tB.discount_desc as discountDesc ,\n" +
            "\tB.discount ,\n" +
            "\tB.discount_price as discountPrice,\n" +
            "\tB.discount_price_start as discountPriceStart,\n" +
            "\tB.discount_price_end as discountPriceEnd,\n" +
            "\tB.code,\n" +
            "\tB.code_name as codeName,\n" +
            "\tB.id as skuId\n" +
            "\tfrom t_shop_goods A\n" +
            "\tjoin t_shop_goods_sku B on A.id  = B.id_goods\n" +
            "\twhere A.disable  = 0\n" +
            "\tand A.is_delete  = 0\n" +
            "\tand B.disable  = 0\n" +
            "\tand A.id_category = ?1 or ?1 is null\n" +
            "\tand A.name like concat('%',?2,'%') \n" +
            ") T1\n" +
            "order by T1.name limit ?3, ?4")
    List<Map<String, Object>> getGoodsPriceList(Long categoryId,String name,int start,int end);

}

